use std::{hash::{DefaultHasher, Hash, Hasher}, path::Path, str::FromStr};
use bevy::log::info;
use rusqlite::Connection;
use soukoban::{Actions, Level};

pub struct Database {
    connection: Connection,
}

impl  Database  {
    pub fn from_file<P: AsRef<Path>>(path: P) -> Self {
        Self { 
            connection: Connection::open(path).expect("failed to open database"),
        }
    }

    pub fn initialize(&self){
        const CREATE_LEVEL_TABLE: &str = "
            create table if not exists tb_level(
                id integer primary key autoincrement,
                title text,
                author text,
                comments text,
                map text not null,
                width integer not null check(width > 0),
                height integer not null check(width > 0),
                hash integer not null unique,
                datetime datetime not null default current_timestamp
            )
        "; 

        const CREATE_LEVEL_INDICES: &str = "
            create unique index if not exists ux_level_hash on tb_level(hash)
        ";

        const CREATE_SNAPSHOT_TABLE: &str = "
            create table if not exists tb_snapshot(
                level_id integer,
                actions text,
                datetime datetime not null default current_timestamp,
                best_move boolean not null default 0 check (best_move in (0,1)),
                best_push boolean not null default 0 check (best_push in (0,1)),
                primary key (level_id,best_move,best_push),
                foreign key (level_id) references tb_level(id) on delete cascade
            )
        ";

        self.connection.execute(CREATE_LEVEL_TABLE, ()).unwrap();
        self.connection.execute(CREATE_LEVEL_INDICES, ()).unwrap();
        self.connection.execute(CREATE_SNAPSHOT_TABLE, ()).unwrap();
    }

    pub fn import_levels(&self,levels: &[Level]){
        self.connection.execute("begin transaction", []).unwrap();
        for level in levels {
            self.import_level(level);
        }
        self.connection.execute("commit", []).unwrap();
    }

    pub fn import_level(&self,level: &Level){
        let title = level.metadata().get("title");
        let author = level.metadata().get("author");
        let comments = level.metadata().get("comments");
        let hash = Database::normalized_hash(level);

        let _ = self.connection.execute(
            "insert into tb_level(title,author,comments,map,width,height,hash) values(?,?,?,?,?,?,?)", 
            (title,author,comments,level.map().to_string(),
                level.map().dimensions().x,
                level.map().dimensions().y,
                hash
            ),
        );
    }



    fn normalized_hash(level: &Level) -> String {
        let mut hasher = DefaultHasher::new();
        let mut normalized_level = level.clone();

        normalized_level.map_mut().normalize();
        normalized_level.map_mut().hash(&mut hasher);
        let hash = hasher.finish();
        hash.to_string()
    } 

    pub fn best_move_solution(&self,level_id: u64) -> Option<Actions>{
        let mut statement = self
            .connection
            .prepare("select actions from tb_snapshot where level_id = ? and best_push = 1")
            .unwrap();
        let mut rows = statement.query([level_id]).unwrap();
        let row = rows.next().unwrap()?;
        let best_push:String = row.get(0).unwrap();
        Some(Actions::from_str(&best_push).unwrap()) 
    }

    pub fn best_push_solution(&self,level_id: u64) -> Option<Actions> {
        let mut statement = self
            .connection
            .prepare("select actions from tb_snapshot where level_id = ? and best_push=1")
            .unwrap();
        let mut rows = statement.query([level_id]).unwrap();
        let row = rows.next().unwrap()?;
        let best_push:String = row.get(0).unwrap();
        Some(Actions::from_str(&best_push).unwrap())
    }


    pub fn get_level_id(&self,level: &Level) -> Option<u64>{
        let hash = Database::normalized_hash(level);
        self.connection
            .query_row("select id from tb_level where hash = ?", [hash],
             |row| {
                row.get(0)
             })
             .ok()
    }

    pub fn get_level_by_id(&self,id: u64) ->Option<Level> {
        let mut statement = self
            .connection
            .prepare("select map,width,height,title,author,comments from tb_level where id = ?")
            .unwrap();

        let mut rows = statement.query([id]).unwrap();
        let row = rows.next().unwrap()?;

        let map = row.get::<_,String>(0).unwrap();
        let mut metadata = String::new();
        if let Ok(title) = row.get::<_,String>(3){
            metadata.push_str(&format!("title: {title}\n"));
        }
        if let Ok(author) = row.get::<_,String>(4) {
            metadata.push_str(&format!("author: {author}\n"));
        }
        if let Ok(comments) = row.get::<_,String>(5) {
            metadata.push_str(&format!("comment:{comments}\ncommend-end:\n"));
        }
        info!("map+metadata : {:?}",&(map.clone() + &metadata));
        let level = Level::from_str(&(map + &metadata)).unwrap();
        Some(level)
    }


    pub fn next_unsolved_level_id(&self,id: u64) -> Option<u64> {
        let mut statement = self.connection.prepare(
            "select id from tb_level where id > ? and id not in (select distinct level_id from tb_snapshot) order by id asc limit 1"
        ).unwrap();

        let mut rows = statement.query([id]).unwrap();

        let row = rows.next().unwrap()?;
        Some(row.get(0).unwrap())
    }

    pub fn max_level_id(&self) -> Option<u64>{
        self.connection
            .query_row("select max(id) from tb_level", 
            [], 
            |row| row.get(0))
            .unwrap()
    }


}